# coding:utf-8

import json
from datetime import date
from src.config.oracle_config import OracleConfig
from src.config.his_robot_config import HisRobotConfig
from src.manager.log_manager import LogManager
from src.manager.oracle_manager import OracleManager
from src.model.model import Model
from src.model.his_robot_account import HisRobotAccount
from src.model.his_robot_account_log import HisRobotAccountLog
from src.model.his_robot_stock_filter import HisRobotStockFilter
from src.model.his_robot_transaction_record import HisRobotTransactionRecord
from src.util.json_util import JsonUtil
from src.util.file_util import FileUtil

Logger = LogManager.get_logger(__name__)


class HisRobotHandler:
    """
    存储model和his开头的表到本地，再导入到数据库中
    """

    def __init__(self):
        """
        构造函数，初始化oracle_manager和cursor对象
        """
        self.oracle_manager = OracleManager(OracleConfig.Username, OracleConfig.Password, OracleConfig.Url)
        self.oracle_manager.connect()
        self.cursor = self.oracle_manager.get_cursor()

    def store_model_in_file(self):
        """
        将model表存储到本地文件中
        """
        Logger.info('将model表存储到本地文件【' + HisRobotConfig.Model_File + '】中')

        # 根据id，查询model表的记录
        self.cursor.execute(
            "select * from MODEL t where t.id between " + str(HisRobotConfig.Model_Id_Begin) + " and " + str(
                HisRobotConfig.Model_Id_End))
        model_list = self.cursor.fetchall()

        # 转换后的list，用于存储到文件中
        store_model_list = list()
        for model_element in model_list:
            model = Model()
            model.id = model_element[0]
            model.name = model_element[1]
            model.create_time = model_element[2].strftime("%Y-%m-%d")
            model.begin_time = model_element[3].strftime("%Y-%m-%d")
            model.end_time = model_element[4].strftime("%Y-%m-%d")
            model.algorithm_description = model_element[5]
            model.s_position_control = model_element[6]
            model.average_annual_return_rate = model_element[7]
            model.buying_long = model_element[8]
            model.short_selling = model_element[9]
            model.success_rate_calculate_time = model_element[10]
            model.success_rate_threshold = model_element[11]
            model.p_single_or_all_method = model_element[12]
            model.p_judge_method = model_element[13]
            model.p_shipping_space_control = model_element[14]
            model.p_percentage_top_threshold = model_element[15]
            model.p_percentage_bottom_threshold = model_element[16]
            model.p_use_min_filter_condition = model_element[17]
            model.type_ = model_element[18]
            model.mandatory_stop_loss = model_element[19]
            model.mandatory_stop_loss_rate = model_element[20]
            model.volatile_rate = model_element[21]
            model.volatile_month_number = model_element[22]
            model.follow_or_reverse_trend = model_element[23]
            model.hold_stock_number_per_account = model_element[24]
            model.account_number = model_element[25]
            model.ma120_not_increasing = model_element[26]
            model.ma120_not_decreasing = model_element[27]
            model.ma250_not_increasing = model_element[28]
            model.ma250_not_decreasing = model_element[29]
            model.c_p_percentage_from_up_dn = model_element[30]
            model.macd_transaction_number = model_element[31]
            model.c_p_ma5_transaction_number = model_element[32]
            model.h_k_a_transaction_number = model_element[33]
            model.kd_transaction_number = model_element[34]
            model.mandatory_stop_profit_rate = model_element[35]
            model.mandatory_stop_profit = model_element[36]
            store_model_list.append(JsonUtil.object_to_dict(model))
        str_json = json.dumps(store_model_list, indent=2, ensure_ascii=False)
        FileUtil.write(HisRobotConfig.Model_File, mode='wb', content=str_json)

        # self.oracle_manager.cursor_close()
        # self.oracle_manager.connect_close()

    def insert_into_model(self):
        """
        从文件中获取数据，插入到model表中
        """
        Logger.info('从文件【' + HisRobotConfig.Model_File + '】中获取数据，插入到model表中')

        file_content = FileUtil.read(HisRobotConfig.Model_File, 'rb')
        model_list = json.loads(file_content)

        # 执行数据库批量插入操作
        param_list = list()
        for mode_dict in model_list:
            param_list.append(
                (mode_dict["id"], mode_dict["name"], date.fromisoformat(mode_dict["create_time"]),
                 date.fromisoformat(mode_dict["begin_time"]),
                 date.fromisoformat(mode_dict["end_time"]),
                 str(mode_dict["algorithm_description"]),
                 None if mode_dict["s_position_control"] is None else float(mode_dict["s_position_control"]),
                 None if mode_dict["average_annual_return_rate"] is None else float(
                     mode_dict["average_annual_return_rate"]),
                 None if mode_dict["buying_long"] is None else float(mode_dict["buying_long"]),
                 None if mode_dict["short_selling"] is None else float(mode_dict["short_selling"]),
                 None if mode_dict["success_rate_calculate_time"] is None else float(
                     mode_dict["success_rate_calculate_time"]),
                 None if mode_dict["success_rate_threshold"] is None else float(mode_dict["success_rate_threshold"]),
                 None if mode_dict["p_single_or_all_method"] is None else float(mode_dict["p_single_or_all_method"]),
                 None if mode_dict["p_judge_method"] is None else float(mode_dict["p_judge_method"]),
                 None if mode_dict["p_shipping_space_control"] is None else float(
                     mode_dict["p_shipping_space_control"]),
                 None if mode_dict["p_percentage_top_threshold"] is None else float(
                     mode_dict["p_percentage_top_threshold"]),
                 None if mode_dict["p_percentage_bottom_threshold"] is None else float(
                     mode_dict["p_percentage_bottom_threshold"]),
                 None if mode_dict["p_use_min_filter_condition"] is None else float(
                     mode_dict["p_use_min_filter_condition"]),
                 None if mode_dict["type_"] is None else float(mode_dict["type_"]),
                 None if mode_dict["mandatory_stop_loss"] is None else float(mode_dict["mandatory_stop_loss"]),
                 None if mode_dict["mandatory_stop_loss_rate"] is None else float(
                     mode_dict["mandatory_stop_loss_rate"]),
                 None if mode_dict["volatile_rate"] is None else float(mode_dict["volatile_rate"]),
                 None if mode_dict["volatile_month_number"] is None else float(mode_dict["volatile_month_number"]),
                 None if mode_dict["follow_or_reverse_trend"] is None else float(mode_dict["follow_or_reverse_trend"]),
                 None if mode_dict["hold_stock_number_per_account"] is None else float(
                     mode_dict["hold_stock_number_per_account"]),
                 None if mode_dict["account_number"] is None else float(mode_dict["account_number"]),
                 None if mode_dict["ma120_not_increasing"] is None else float(mode_dict["ma120_not_increasing"]),
                 None if mode_dict["ma120_not_decreasing"] is None else float(mode_dict["ma120_not_decreasing"]),
                 None if mode_dict["ma250_not_increasing"] is None else float(mode_dict["ma250_not_increasing"]),
                 None if mode_dict["ma250_not_decreasing"] is None else float(mode_dict["ma250_not_decreasing"]),
                 None if mode_dict["c_p_percentage_from_up_dn"] is None else float(
                     mode_dict["c_p_percentage_from_up_dn"]),
                 None if mode_dict["macd_transaction_number"] is None else float(mode_dict["macd_transaction_number"]),
                 None if mode_dict["c_p_ma5_transaction_number"] is None else float(
                     mode_dict["c_p_ma5_transaction_number"]),
                 None if mode_dict["h_k_a_transaction_number"] is None else float(
                     mode_dict["h_k_a_transaction_number"]),
                 None if mode_dict["kd_transaction_number"] is None else float(mode_dict["kd_transaction_number"]),
                 None if mode_dict["mandatory_stop_profit_rate"] is None else float(
                     mode_dict["mandatory_stop_profit_rate"]),
                 None if mode_dict["mandatory_stop_profit"] is None else float(mode_dict["mandatory_stop_profit"])))
        self.oracle_manager.batch_insert("insert into MODEL(id, "
                                         "name, "
                                         "create_time, "
                                         "begin_time, "
                                         "end_time, "
                                         "algorithm_description, "
                                         "s_position_control, "
                                         "average_annual_return_rate, "
                                         "buying_long, "
                                         "short_selling, "
                                         "success_rate_calculate_time, "
                                         "success_rate_threshold, "
                                         "p_single_or_all_method, "
                                         "p_judge_method, "
                                         "p_shipping_space_control, "
                                         "p_percentage_top_threshold, "
                                         "p_percentage_bottom_threshold, "
                                         "p_use_min_filter_condition, "
                                         "type_, "
                                         "mandatory_stop_loss, "
                                         "mandatory_stop_loss_rate, "
                                         "volatile_rate, "
                                         "volatile_month_number, "
                                         "follow_or_reverse_trend, "
                                         "hold_stock_number_per_account, "
                                         "account_number, "
                                         "ma120_not_increasing, "
                                         "ma120_not_decreasing, "
                                         "ma250_not_increasing, "
                                         "ma250_not_decreasing, "
                                         "c_p_percentage_from_up_dn, "
                                         "macd_transaction_number, "
                                         "c_p_ma5_transaction_number, "
                                         "h_k_a_transaction_number, "
                                         "kd_transaction_number, "
                                         "mandatory_stop_profit_rate, "
                                         "mandatory_stop_profit) "
                                         "values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, "
                                         ":15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, "
                                         ":29, :30, :31, :32, :33, :34, :35, :36, :37)",
                                         param_list)

        # self.oracle_manager.cursor_close()
        # self.oracle_manager.connect_close()

    def store_his_robot_account_in_file(self):
        """
        将his_robot_account表存储到本地文件中
        """
        Logger.info('将his_robot_account表存储到本地文件【' + HisRobotConfig.His_Robot_Account_File + '】中')

        # 根据model_id，查询his_robot_account表的记录
        self.cursor.execute(
            "select * from his_robot_account t where t.model_id between " + str(
                HisRobotConfig.His_Robot_Account_Model_Id_Begin)
            + " and " + str(HisRobotConfig.His_Robot_Account_Model_Id_End))
        his_robot_account_list = self.cursor.fetchall()

        # 转换后的list，用于存储到文件中
        store_his_robot_account_list = list()
        for his_robot_account_element in his_robot_account_list:
            his_robot_account = HisRobotAccount()
            his_robot_account.id_ = his_robot_account_element[0]
            his_robot_account.robot_name = his_robot_account_element[1]
            his_robot_account.hold_stock_number = his_robot_account_element[2]
            his_robot_account.stock_assets = his_robot_account_element[3]
            his_robot_account.capital_assets = his_robot_account_element[4]
            his_robot_account.total_assets = his_robot_account_element[5]
            his_robot_account.model_id = his_robot_account_element[6]
            store_his_robot_account_list.append(JsonUtil.object_to_dict(his_robot_account))
        str_json = json.dumps(store_his_robot_account_list, indent=2, ensure_ascii=False)
        FileUtil.write(HisRobotConfig.His_Robot_Account_File, mode='wb', content=str_json)

        # self.oracle_manager.cursor_close()
        # self.oracle_manager.connect_close()

    def insert_into_his_robot_account(self):
        """
        从文件中获取数据，插入到his_robot_account表中
        """
        Logger.info('从文件【' + HisRobotConfig.His_Robot_Account_File + '】中获取数据，插入到his_robot_account表中')

        file_content = FileUtil.read(HisRobotConfig.His_Robot_Account_File, 'rb')
        his_robot_account_list = json.loads(file_content)

        # 执行数据库批量插入操作
        param_list = list()
        for his_robot_account_dict in his_robot_account_list:
            param_list.append(
                # (his_robot_account_dict["id_"], his_robot_account_dict["robot_name"],
                (his_robot_account_dict["robot_name"],
                 his_robot_account_dict["hold_stock_number"], his_robot_account_dict["stock_assets"],
                 his_robot_account_dict["capital_assets"], his_robot_account_dict["total_assets"],
                 his_robot_account_dict["model_id"]))
        # self.oracle_manager.batch_insert("insert into his_robot_account(id_, "
        self.oracle_manager.batch_insert("insert into his_robot_account("
                                         "robot_name, "
                                         "hold_stock_number, "
                                         "stock_assets, "
                                         "capital_assets, "
                                         "total_assets, "
                                         "model_id) "
                                         # "values (:1, :2, :3, :4, :5, :6, :7)",
                                         "values (:1, :2, :3, :4, :5, :6)",
                                         param_list)

        # self.oracle_manager.cursor_close()
        # self.oracle_manager.connect_close()

    def store_his_robot_account_log_in_file(self):
        """
        将his_robot_account_log表存储到本地文件中
        """
        Logger.info('将his_robot_account_log表存储到本地文件【' + HisRobotConfig.His_Robot_Account_Log_File + '】中')

        # 根据model_id，查询his_robot_account_log表的记录
        self.cursor.execute(
            "select * from his_robot_account_log t where t.model_id between " + str(
                HisRobotConfig.His_Robot_Account_Log_Model_Id_Begin)
            + " and " + str(HisRobotConfig.His_Robot_Account_Log_Model_Id_End))
        his_robot_account_log_list = self.cursor.fetchall()

        # 转换后的list，用于存储到文件中
        store_his_robot_account_log_list = list()
        for his_robot_account_log_element in his_robot_account_log_list:
            his_robot_account_log = HisRobotAccountLog()
            his_robot_account_log.id_ = his_robot_account_log_element[0]
            his_robot_account_log.date_ = his_robot_account_log_element[1].strftime("%Y-%m-%d")
            his_robot_account_log.robot_name = his_robot_account_log_element[2]
            his_robot_account_log.hold_stock_number = his_robot_account_log_element[3]
            his_robot_account_log.stock_assets = his_robot_account_log_element[4]
            his_robot_account_log.capital_assets = his_robot_account_log_element[5]
            his_robot_account_log.total_assets = his_robot_account_log_element[6]
            his_robot_account_log.model_id = his_robot_account_log_element[7]
            store_his_robot_account_log_list.append(JsonUtil.object_to_dict(his_robot_account_log))
        str_json = json.dumps(store_his_robot_account_log_list, indent=2, ensure_ascii=False)
        FileUtil.write(HisRobotConfig.His_Robot_Account_Log_File, mode='wb', content=str_json)

        # self.oracle_manager.cursor_close()
        # self.oracle_manager.connect_close()

    def insert_into_his_robot_account_log(self):
        """
        从文件中获取数据，插入到his_robot_account_log表中
        """
        Logger.info('从文件【' + HisRobotConfig.His_Robot_Account_Log_File + '】中获取数据，插入到his_robot_account_log表中')

        file_content = FileUtil.read(HisRobotConfig.His_Robot_Account_Log_File, 'rb')
        his_robot_account_log_list = json.loads(file_content)

        # 执行数据库批量插入操作
        param_list = list()
        for his_robot_account_log_dict in his_robot_account_log_list:
            param_list.append(
                # (his_robot_account_log_dict["id_"], date.fromisoformat(his_robot_account_log_dict["date_"]),
                (date.fromisoformat(his_robot_account_log_dict["date_"]),
                 his_robot_account_log_dict["robot_name"],#.decode('gbk'),
                 his_robot_account_log_dict["hold_stock_number"],
                 his_robot_account_log_dict["stock_assets"], his_robot_account_log_dict["capital_assets"],
                 his_robot_account_log_dict["total_assets"], his_robot_account_log_dict["model_id"]))
        # self.oracle_manager.batch_insert("insert into his_robot_account_log(id_,"
            self.oracle_manager.batch_insert("insert into his_robot_account_log("
                                         "date_, "
                                         "robot_name, "
                                         "hold_stock_number, "
                                         "stock_assets, "
                                         "capital_assets, "
                                         "total_assets, "
                                         "model_id) "
                                         # "values (:1, :2, :3, :4, :5, :6, :7, :8)",
                                             "values (:1, :2, :3, :4, :5, :6, :7)",
                                         param_list)

        # self.oracle_manager.cursor_close()
        # self.oracle_manager.connect_close()

    def store_his_robot_stock_filter_in_file(self):
        """
        将his_robot_stock_filter表存储到本地文件中
        """
        Logger.info('将his_robot_stock_filter表存储到本地文件【' + HisRobotConfig.His_Robot_Stock_Filter_File + '】中')

        # 根据model_id，查询his_robot_stock_filter表的记录
        self.cursor.execute(
            "select * from his_robot_stock_filter t where t.model_id between " + str(
                HisRobotConfig.His_Robot_Stock_Filter_Model_Id_Begin)
            + " and " + str(HisRobotConfig.His_Robot_Stock_Filter_Model_Id_End))
        his_robot_stock_filter_list = self.cursor.fetchall()

        # 转换后的list，用于存储到文件中
        store_his_robot_stock_filter_list = list()
        for his_robot_stock_filter_element in his_robot_stock_filter_list:
            his_robot_stock_filter = HisRobotStockFilter()
            his_robot_stock_filter.id_ = his_robot_stock_filter_element[0]
            his_robot_stock_filter.stock_code = his_robot_stock_filter_element[1]
            his_robot_stock_filter.model_id = his_robot_stock_filter_element[2]
            store_his_robot_stock_filter_list.append(JsonUtil.object_to_dict(his_robot_stock_filter))
        str_json = json.dumps(store_his_robot_stock_filter_list, indent=2, ensure_ascii=False)
        FileUtil.write(HisRobotConfig.His_Robot_Stock_Filter_File, mode='wb', content=str_json)

        # self.oracle_manager.cursor_close()
        # self.oracle_manager.connect_close()

    def insert_into_his_robot_stock_filter(self):
        """
        从文件中获取数据，插入到his_robot_stock_filter表中
        """
        Logger.info('从文件【' + HisRobotConfig.His_Robot_Stock_Filter_File + '】中获取数据，插入到his_robot_stock_filter表中')

        file_content = FileUtil.read(HisRobotConfig.His_Robot_Stock_Filter_File, 'rb')
        his_robot_stock_filter_list = json.loads(file_content)

        # 执行数据库批量插入操作
        param_list = list()
        for his_robot_stock_filter_dict in his_robot_stock_filter_list:
            param_list.append(
                (his_robot_stock_filter_dict["id_"], his_robot_stock_filter_dict["stock_code"],
                 his_robot_stock_filter_dict["model_id"]))
        self.oracle_manager.batch_insert("insert into his_robot_stock_filter(id_, "
                                         "stock_code, "
                                         "model_id) "
                                         "values (:1, :2, :3)",
                                         param_list)

        # self.oracle_manager.cursor_close()
        # self.oracle_manager.connect_close()

    def store_his_robot_transaction_record_in_file(self):
        """
        将his_robot_transaction_record表存储到本地文件中
        """
        try:
            Logger.info(
                '将his_robot_transaction_record表存储到本地文件【' + HisRobotConfig.His_Robot_Transaction_Record_File + '】中')

            # 根据model_id，查询his_robot_transaction_record表的记录
            self.cursor.execute(
                "select * from his_robot_transaction_record t where t.model_id between " + str(
                    HisRobotConfig.His_Robot_Transaction_Record_Model_Id_Begin)
                + " and " + str(HisRobotConfig.His_Robot_Transaction_Record_Model_Id_End))
            his_robot_transaction_record_list = self.cursor.fetchall()

            # 转换后的list，用于存储到文件中
            store_his_robot_transaction_record_list = list()
            for his_robot_transaction_record_element in his_robot_transaction_record_list:
                his_robot_transaction_record = HisRobotTransactionRecord()
                his_robot_transaction_record.id_ = his_robot_transaction_record_element[0]
                his_robot_transaction_record.robot_name = his_robot_transaction_record_element[1]
                his_robot_transaction_record.stock_code = his_robot_transaction_record_element[2]
                if his_robot_transaction_record_element[3] is None:
                    his_robot_transaction_record.buy_date = None
                else:
                    his_robot_transaction_record.buy_date = his_robot_transaction_record_element[3].strftime("%Y-%m-%d")
                his_robot_transaction_record.buy_price = his_robot_transaction_record_element[4]
                his_robot_transaction_record.buy_amount = his_robot_transaction_record_element[5]
                if his_robot_transaction_record_element[6] is None:
                    his_robot_transaction_record.sell_date = None
                else:
                    his_robot_transaction_record.sell_date = his_robot_transaction_record_element[6].strftime(
                        "%Y-%m-%d")
                his_robot_transaction_record.sell_price = his_robot_transaction_record_element[7]
                his_robot_transaction_record.sell_amount = his_robot_transaction_record_element[8]
                his_robot_transaction_record.filter_type = his_robot_transaction_record_element[9]
                his_robot_transaction_record.direction = his_robot_transaction_record_element[10]
                his_robot_transaction_record.profit_and_loss = his_robot_transaction_record_element[11]
                his_robot_transaction_record.profit_and_loss_rate = his_robot_transaction_record_element[12]
                his_robot_transaction_record.model_id = his_robot_transaction_record_element[13]
                store_his_robot_transaction_record_list.append(JsonUtil.object_to_dict(his_robot_transaction_record))
            str_json = json.dumps(store_his_robot_transaction_record_list, indent=2, ensure_ascii=False)
            FileUtil.write(HisRobotConfig.His_Robot_Transaction_Record_File, mode='wb', content=str_json)

            # self.oracle_manager.cursor_close()
            # self.oracle_manager.connect_close()
        except Exception as e:
            Logger.error(e)
        else:
            pass
        finally:
            pass

    def insert_into_his_robot_transaction_record(self):
        """
        从文件中获取数据，插入到his_robot_transaction_record表中
        """
        try:
            Logger.info(
                '从文件【' + HisRobotConfig.His_Robot_Transaction_Record_File + '】中获取数据，插入到his_robot_transaction_record表中')

            file_content = FileUtil.read(HisRobotConfig.His_Robot_Transaction_Record_File, 'rb')
            his_robot_transaction_record_list = json.loads(file_content)

            # 执行数据库批量插入操作
            param_list = list()
            for his_robot_transaction_record_dict in his_robot_transaction_record_list:
                param_list.append(
                    (his_robot_transaction_record_dict["id_"], his_robot_transaction_record_dict["robot_name"],
                     his_robot_transaction_record_dict["stock_code"],
                     None if his_robot_transaction_record_dict["buy_date"] is None else date.fromisoformat(
                         his_robot_transaction_record_dict["buy_date"]),
                     his_robot_transaction_record_dict["buy_price"], his_robot_transaction_record_dict["buy_amount"],
                     None if his_robot_transaction_record_dict["sell_date"] is None else date.fromisoformat(
                         his_robot_transaction_record_dict["sell_date"]),
                     his_robot_transaction_record_dict["sell_price"],
                     his_robot_transaction_record_dict["sell_amount"], his_robot_transaction_record_dict["filter_type"],
                     his_robot_transaction_record_dict["direction"],
                     his_robot_transaction_record_dict["profit_and_loss"],
                     his_robot_transaction_record_dict["profit_and_loss_rate"],
                     his_robot_transaction_record_dict["model_id"]))
            self.oracle_manager.batch_insert(
                "insert into his_robot_transaction_record(id_, robot_name, stock_code, buy_date, buy_price, "
                "buy_amount, sell_date, sell_price, sell_amount, filter_type, direction, profit_and_loss, "
                "profit_and_loss_rate, model_id) "
                "values (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14)",
                param_list)

            # self.oracle_manager.cursor_close()
            # self.oracle_manager.connect_close()
        except Exception as e:
            Logger.error(e)
        else:
            pass
        finally:
            pass
